package com.scuec.trainingmanagementsys.mapper;
import com.scuec.trainingmanagementsys.entity.WeeklyReport;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

@Mapper
public interface WeeklyReportMapper {
    // 添加周报
    @Insert("INSERT INTO weeklyreport " +
            "(WeeklyReportContent, TeamID, ProjectID, StudentID, WrCreateTime) " +
            "VALUES (#{weeklyReportContent}, #{teamID}, #{projectID}, #{studentID}, #{wrCreateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "weeklyReportID")
    int addWeeklyReport(WeeklyReport weeklyReport);

    // 删除周报
    @Delete("DELETE FROM weeklyreport WHERE WeeklyReportID = #{weeklyReportID}")
    int deleteWeeklyReport(Integer weeklyReportID);

    // 更新周报
    @Update("UPDATE weeklyreport " +
            "SET WeeklyReportContent = #{weeklyReportContent} " +
            "WHERE WeeklyReportID = #{weeklyReportID}")
    int updateWeeklyReport(WeeklyReport weeklyReport);

    // 根据ID查询周报
    @Select("SELECT * FROM weeklyreport WHERE WeeklyReportID = #{weeklyReportID}")
    WeeklyReport getWeeklyReportById(Integer weeklyReportID);

    // 查询团队所有周报（带关联信息）
    @Select("SELECT " +
            "wr.WeeklyReportID, wr.WeeklyReportContent, wr.WrCreateTime, wr.TeamID, " +
            "wr.ProjectID, wr.StudentID, " +
            "t.teamName, s.studentName, p.projectName " +
            "FROM weeklyreport wr " +
            "LEFT JOIN team t ON wr.TeamID = t.teamID " +
            "LEFT JOIN student s ON wr.StudentID = s.studentID " +
            "LEFT JOIN project p ON wr.ProjectID = p.ProjectID " +
            "WHERE wr.TeamID = #{teamID} " +
            "ORDER BY wr.WrCreateTime DESC")
    List<WeeklyReport> getWeeklyReportsByTeamId(Integer teamID);

    // 查询学生提交的周报
    @Select("SELECT " +
            "wr.WeeklyReportID, wr.WeeklyReportContent, wr.WrCreateTime, wr.TeamID, " +
            "wr.ProjectID, wr.StudentID, " +
            "t.teamName, s.studentName, p.projectName " +
            "FROM weeklyreport wr " +
            "LEFT JOIN team t ON wr.TeamID = t.teamID " +
            "LEFT JOIN student s ON wr.StudentID = s.studentID " +
            "LEFT JOIN project p ON wr.ProjectID = p.ProjectID " +
            "WHERE wr.StudentID = #{studentID} " +
            "ORDER BY wr.WrCreateTime DESC")
    List<WeeklyReport> getWeeklyReportsByStudentId(Integer studentID);

    // 检查周报是否属于该用户
    @Select("SELECT COUNT(*) FROM weeklyreport " +
            "WHERE WeeklyReportID = #{weeklyReportID} AND StudentID = #{studentID}")
    int isReportOwner(@Param("weeklyReportID") Integer weeklyReportID, @Param("studentID") Integer studentID);

    // 查询当前学生的周报信息，关联查询student、project和team表
    @Select("SELECT " +
            "wr.WeeklyReportID, wr.WeeklyReportContent, wr.WrCreateTime, wr.TeamID, " +
            "wr.ProjectID, wr.StudentID, " +
            "t.teamName, s.studentName, p.projectName " +
            "FROM weeklyreport wr " +
            "LEFT JOIN team t ON wr.TeamID = t.teamID " +
            "LEFT JOIN student s ON wr.StudentID = s.studentID " +
            "LEFT JOIN project p ON wr.ProjectID = p.ProjectID " +
            "WHERE wr.StudentID = #{studentID} " +
            "ORDER BY wr.WrCreateTime DESC")
    List<WeeklyReport> getWeeklyReportsByStudentIdWithRelations(Integer studentID);

    @Select("SELECT " +
            "wr.weeklyReportID, wr.weeklyReportContent, wr.wrCreateTime, " +
            "wr.teamID, wr.projectID, wr.studentID, " +
            "t.teamName, " +
            "s.studentName, " +
            "p.projectName AS projectName " +
            "FROM weeklyreport wr " +
            "LEFT JOIN team t ON wr.teamID = t.teamID " +
            "LEFT JOIN student s ON wr.studentID = s.studentID " +
            "LEFT JOIN project p ON wr.projectID = p.projectID " +
            "ORDER BY wr.wrCreateTime DESC")
    List<WeeklyReport> getAllWeeklyReports();

    // 根据项目ID获取周报信息
    @Select("SELECT " +
            "wr.WeeklyReportID, wr.WeeklyReportContent, wr.WrCreateTime, wr.TeamID, " +
            "wr.ProjectID, wr.StudentID, " +
            "t.teamName, s.studentName, p.projectName " +
            "FROM weeklyreport wr " +
            "LEFT JOIN team t ON wr.TeamID = t.teamID " +
            "LEFT JOIN student s ON wr.StudentID = s.studentID " +
            "LEFT JOIN project p ON wr.ProjectID = p.ProjectID " +
            "WHERE wr.ProjectID = #{projectId} " +
            "ORDER BY wr.WrCreateTime DESC")
    List<WeeklyReport> getWeeklyReportsByProjectId(Integer projectId);

    @Select("SELECT * FROM weeklyassessment WHERE weeklyreportid = #{weeklyReportID}")
    List<Map<String, Object>> getCommentsByReportId(Integer weeklyReportID);

    @Select("SELECT DATE(WrCreateTime) as date, COUNT(*) as count " +
            "FROM weeklyreport " +
            "WHERE WrCreateTime >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) " +
            "GROUP BY DATE(WrCreateTime) " +
            "ORDER BY DATE(WrCreateTime)")
    List<Map<String, Object>> selectWeeklyCountLast7Days();

    @Select("SELECT " +
            "wr.WeeklyReportID, wr.WeeklyReportContent, wr.WrCreateTime, wr.TeamID, " +
            "wr.ProjectID, wr.StudentID, " +
            "t.teamName, s.studentName, p.projectName " +
            "FROM weeklyreport wr " +
            "LEFT JOIN team t ON wr.TeamID = t.teamID " +
            "LEFT JOIN student s ON wr.StudentID = s.studentID " +
            "LEFT JOIN project p ON wr.ProjectID = p.ProjectID " +
            "WHERE wr.TeamID = #{teamID} " +
            "ORDER BY wr.WrCreateTime DESC")
    List<WeeklyReport> getWeeklyReportsByTeamIdWithRelations(Integer teamID);}